import { db } from '@/lib/db';

export const getSecu = async (secuCode) => {
    const secu = await db.dest_current.findUnique({
        select: {
            secu_code: true,
            secu_name: true,
            tokens: true
        },
        where: {
            secu_code: secuCode
        }
    });
    return secu;
};

export const getSecuFromView = async (secuCode) => {
    const result = await db.$queryRaw`
    select secu_code,secu_name,hy,tokens,expect,expect_brief,array(select * from unnest(expect) where unnest = any(expect_brief)) as expect_cross
    FROM v_expect
    WHERE secu_code = ${secuCode}
    `;
    return result.length === 1 ? result[0] : null;
};

export const getExpectCrossFromView = async () => {
    const items = await db.$queryRaw`
    SELECT secu_code,secu_name,prev_up_num,prev_lbn,clbn,expect_cross 
    FROM v_expect 
    WHERE  expect_cross!='{}' and clbn>=2 or (was_dt=1 and zt=1) or (prev_lbn<=-1 and zt=1) and not starts_with(secu_name,'ST')
    ORDER BY prev_up_num DESC, prev_lbn DESC,clbn DESC
    `;
    return items;
};

export const getKindsByToken = async (tokens) => {
    if (!tokens || tokens.length === 0) return [];
    const items = await db.$queryRaw`
    SELECT secu_code,secu_name,prev_up_num,prev_lbn,clbn,common FROM (
        SELECT secu_code,secu_name,prev_up_num,prev_lbn,clbn,ARRAY(select * FROM unnest(${tokens}) WHERE UNNEST = any(tokens)) as common 
        FROM v_expect
        WHERE (zt=1 or dt=1 or prev_lbn!=0) and not starts_with(secu_name,'ST')
    ) 
    WHERE common!='{}'
    ORDER BY prev_up_num DESC, prev_lbn DESC,clbn DESC
    `;
    return items;
};

export const getSecuInfo = async (secuCode) => {
    if (!secuCode) return null;
    const result = await db.$queryRaw`
        SELECT 
            info.secu_code AS secu_code,
            secu_name,
            ltsz,
            describe,
            profitable_business,
            product_name,
            main_business,
            business_nature,
            hy,
            hy2,
            price,
            tokens,
            expect
        FROM tb_info info 
            LEFT JOIN (select secu_code,clbn,price FROM dest_current) dc on info.secu_code=dc.secu_code 
            LEFT JOIN tb_token tk on info.secu_code = tk.secu_code
            LEFT JOIN tb_expect eps on info.secu_code = eps.secu_code
        WHERE info.secu_code = ${secuCode}
    `;
    return result.length === 1 ? result[0] : null;
};

export const getSecuBlocks = async (secuCode) => {
    if (!secuCode) return null;
    const result = await db.$queryRaw`
        SELECT block_name,block_source,block_explain
        FROM tb_block
        WHERE secu_code = ${secuCode} and updated_at >= now() - '10 days'::interval
    `;
    return result;
};
export const getKplRecentBlocks = async (num = 5) => {
    const result = await db.$queryRaw`
    with
        cte_cal as (select array_agg(cal_date) as dates from (
                        select cal_date from tb_calendar where is_open=1 and cal_date<=current_timestamp::date order by cal_date desc limit ${num})
                        ),
        cte_kpl_recent as (select array_unique(array_accum(array[no_1,no_2,no_3,no_4])) as recent_tops from tb_rank_hist,cte_cal where rank_date = any(cte_cal.dates))
    select recent_tops from cte_kpl_recent
    `;
    // console.log(result);
    const { recent_tops } = result[0];
    return recent_tops.length > 1 ? recent_tops : [];
};
export const getAllBlocks = async () => {
    const result = await db.$queryRaw`
        select array_unique(array_accum(tokens)) as tokens from tb_token;
    `;
    const tokens = result.length === 1 ? result[0].tokens : [];
    return tokens;
};

export const getZTSecus = async () => {
    const result = await db.$queryRaw`
        SELECT secu_code,secu_name,prev_up_num,prev_lbn,clbn FROM v_expect WHERE zt=1 and not starts_with(secu_name,'ST') ORDER BY prev_up_num DESC, prev_lbn DESC,clbn DESC
    `;
    return result;
};

export const getTopRank = async () => {
    const result = await db.$queryRaw`
    SELECT rank_data FROM tb_rank WHERE rank_name='kpl_jx'
    `;
    if (result.length === 0) return [];
    const objs = result[0].rank_data || [];
    const tops = objs.map((el, idx) => (idx <= 3 ? el['plate_name'] : '')).filter((el) => el !== '');
    return tops;
};

export const getClbn = async (clbn = 1) => {
    const result = await db.$queryRaw`
    select secu_code,secu_name,cup_num,clbn,b1amt,tokens,expect from v_expect
    where clbn=${clbn} order by clbn desc, b1amt desc
    `;
    if (!result) return [];
    return result;
};

export const getAnalyze = async () => {
    const result = await db.$queryRaw`
    with
        cte_kpl as (
        select array_agg(top_block)::varchar[] as top_ranks from (
        select jsonb_extract_path_text(jsonb_array_elements(rank_data),'plate_name') as top_block from tb_rank where rank_name='kpl_jx' limit 4
        )),
        cte_tops as (select array_accum(expect) as top_eps from v_expect where zt=1 and (clbn>=2 or  is_top=true)),
        cte_arts as (select array_accum(art_secus) as names from tb_article where created_at>=now()-'6 hours'::interval ),
        cte_name_counter as (select name,count(c) as cnt from (select unnest(names) as name,1 as c from cte_arts) group by name),
        cte_curr as (select secu_code,secu_name,cup_num,clbn,b1amt,hy,tokens,array_subtract(expect,top_eps) as neps,array_intersect(top_ranks,tokens) as vib_ranks,array_intersect(top_eps,expect) as vib_tops from v_expect,cte_tops,cte_kpl where not starts_with(secu_code,'688') and clbn>=1)
    select secu_name,cnt,cur.secu_code,hy,vib_ranks,neps,vib_tops,tokens
    from  cte_curr as cur left join cte_name_counter as cc on cur.secu_name=cc.name
    where clbn=1
    order by clbn desc, case when clbn>=1 then b1amt else 0 end desc,cnt desc
    limit 5;
    `;
    if (!result) return [];
    return result;
};
export const getAnalyzeLarge = async () => {
    const result = await db.$queryRaw`
    with
        cte_kpl as (
        select array_agg(top_block)::varchar[] as top_ranks from (
        select jsonb_extract_path_text(jsonb_array_elements(rank_data),'plate_name') as top_block from tb_rank where rank_name='kpl_jx' limit 4
        ))
    select secu_name,expect,tokens,array_intersect(top_ranks,tokens) as vib from v_expect,cte_kpl where price>avg_price and chg>=2 and ltsz>=300 and expect!='{}' and array_intersect(top_ranks,tokens) !='{}' order by chg desc limit 5;
    `;
    if (!result) return [];
    return result;
};

export async function getNegatives() {
    const stocks = await db.$queryRaw`
    SELECT secu_code,secu_name,chg,hy,prev_lbn,expect,expect_brief,tokens 
    FROM v_expect
    WHERE (prev_lbn>=1 or chg15>=100) and (chg<-5 or dt=1)
    `;
    return stocks || [];
}

export async function hasJianChi(secu_code) {
    const result = await db.$queryRaw`
    SELECT * FROM tb_zjc_plan 
    WHERE secu_code = ${secu_code} and current_timestamp::date + '1 day'::interval >= start_date and current_timestamp::date<= end_date ; 
    `;
    return result.length > 0;
}
